# -*- coding: utf-8 -*-
from openerp import tools
from openerp.osv import fields, osv


class sale_package_count(osv.osv):
    # 采购单产品毛利统计
    _name = 'sale.package.count'
    _description = u'采购到货率'
    _auto = False

    _columns = {
        'month': fields.char(string=u'月份', readonly=True),
        'name': fields.char(string=u'姓名', readonly=True),
        'vip_partner_amount_total': fields.float(string='VIP客户销售额', readonly=True),
        'vip_partner_origin_amount': fields.float(string='VIP客户包装原价', readonly=True),
        'vip_partner_package_price': fields.float(string='VIP客户包装销售价', readonly=True),
        'vip_partner_package_free_rate': fields.float(string='VIP客户包装未收费占比(%)', readonly=True),
        'not_vip_partner_amount_total': fields.float(string='非VIP客户销售额', readonly=True),
        'not_vip_partner_origin_amount': fields.float(string='非VIP客户包装原价', readonly=True),
        'not_vip_partner_package_price': fields.float(string='非VIP客户包装销售价', readonly=True),
        'not_vip_partner_package_free_rate': fields.float(string='非VIP客户包装未收费占比(%)', readonly=True),

    }

    # 建立视图
    def init(self, cr):
        tools.drop_view_if_exists(cr, self._table)
        cr.execute('''CREATE OR REPLACE VIEW %s AS (
SELECT
  ss.id AS id,
  ss.confirm_month AS month,
  ru.login      AS name,
  ss.vip_partner_amount_total,
  ss.vip_partner_origin_amount,
  ss.vip_partner_package_price,
  (CASE WHEN ss.vip_partner_amount_total > 0
    THEN round((ss.vip_partner_origin_amount - ss.vip_partner_package_price) :: NUMERIC / ss.vip_partner_amount_total * 100, 3)
   ELSE 0 END)  AS vip_partner_package_free_rate,
  ss.not_vip_partner_amount_total,
  ss.not_vip_partner_origin_amount,
  ss.not_vip_partner_package_price,
  (CASE WHEN ss.not_vip_partner_amount_total > 0
    THEN round(
        (ss.not_vip_partner_origin_amount - ss.not_vip_partner_package_price) :: NUMERIC / ss.not_vip_partner_amount_total * 100,
        3)
   ELSE 0 END)  AS not_vip_partner_package_free_rate
FROM (
       SELECT
         min(s.id) AS id,
         s.confirm_month,
         s.user_id,
         sum(CASE WHEN cust_grade = 'VIP客户'
           THEN normal_product_sum
             ELSE 0 END) AS vip_partner_amount_total,
         sum(CASE WHEN cust_grade = 'VIP客户'
           THEN package_product_sum
             ELSE 0 END) AS vip_partner_package_price,
         sum(CASE WHEN cust_grade = 'VIP客户'
           THEN origin_price_sum
             ELSE 0 END) AS vip_partner_origin_amount,
         sum(CASE WHEN cust_grade <> 'VIP客户'
           THEN normal_product_sum
             ELSE 0 END) AS not_vip_partner_amount_total,
         sum(CASE WHEN cust_grade <> 'VIP客户'
           THEN package_product_sum
             ELSE 0 END) AS not_vip_partner_package_price,
         sum(CASE WHEN cust_grade <> 'VIP客户'
           THEN origin_price_sum
             ELSE 0 END) AS not_vip_partner_origin_amount
       FROM (
              SELECT
                MIN(so.id) AS id,
                to_char(sol.order_confirm_date, 'YYYY-MM') AS confirm_month,
                so.user_id,
                so.cust_grade,
                sum(CASE WHEN pt.categ_id <> 180
                  THEN sol.price_subtotal
                    ELSE 0 END)                            AS normal_product_sum,
                sum(CASE WHEN pt.categ_id = 180
                  THEN sol.price_subtotal
                    ELSE 0 END)                            AS package_product_sum,
                sum(CASE WHEN pt.categ_id = 180
                  THEN sol.origin_price * sol.product_uom_qty
                    ELSE 0 END)                            AS origin_price_sum
              FROM sale_order so
                INNER JOIN sale_order_line sol ON sol.order_id = so.id
                INNER JOIN product_product pp ON pp.id = sol.product_id
                INNER JOIN product_template pt ON pt.id = pp.product_tmpl_id
              WHERE so.need_package = '是' AND so.real_so_id IS NULL AND so.state NOT IN ('draft', 'cancel')
              GROUP BY to_char(sol.order_confirm_date, 'YYYY-MM'), so.user_id, so.cust_grade
            ) s
       GROUP BY s.confirm_month, user_id
     ) ss INNER JOIN res_users ru ON ru.id = ss.user_id
WHERE (ss.vip_partner_amount_total > 0 OR ss.not_vip_partner_amount_total > 0)
ORDER BY ss.confirm_month, ru.login)
              ''' % self._table)





